--TAO DATABASE QUANLINHANVIEN

CREATE DATABASE QUANLINHANVIEN

--SU DUNG DATABASE QUANLINHANVIEN

USE QUANLINHANVIEN
--TAO TABLE DEAN

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='DEAN')
BEGIN 
	DROP TABLE DEAN
END
GO 
CREATE TABLE DEAN
(
	TENDA NVARCHAR(30),
	MADA  INT,
	DDIEM_DA NVARCHAR(30),
	PHONG	INT
	PRIMARY KEY (MADA)
)

--THEM DU LIEU CHO TABLE DEAN

INSERT INTO DEAN VALUES('SAN PHAM Y',2,'NHATRANG',1)
INSERT INTO DEAN VALUES('SAN PHAM Z',3,'TP HCM',5)
INSERT INTO DEAN VALUES('SAN PHAM X',5,'VUNG TAU',1)
INSERT INTO DEAN VALUES('TIN HOC HOA',10,'HA NOI',4)
INSERT INTO DEAN VALUES('HOA HOC',40,'BIEN HOA',5)
UPDATE DEAN SET DDIEM_DA='NHA TRANG' WHERE MADA=2
SELECT * FROM DEAN

--TAO TABLE DIADIEM_PHG

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='DIADIEM_PHG')
BEGIN 
	DROP TABLE DEAN
END
GO 
CREATE TABLE DIADIEM_PHG
(
	MAPHG INT,
	DIADIEM NVARCHAR(30)
	PRIMARY KEY (MAPHG,DIADIEM)
)

--THEM DU LIEU CHO TABLE DIADIEM_PHG

INSERT INTO DIADIEM_PHG VALUES(1,'VUNG TAU')
INSERT INTO DIADIEM_PHG VALUES(2,'TP HCM')
INSERT INTO DIADIEM_PHG VALUES(3,'NHA TRANG')
INSERT INTO DIADIEM_PHG VALUES(4,'TP HCM')
INSERT INTO DIADIEM_PHG VALUES(5,'HA NOI')
INSERT INTO DIADIEM_PHG VALUES(5,'TP HCM')
SELECT * FROM DIADIEM_PHG

--TAO TABLE NHANVIEN

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='NHANVIEN')
BEGIN 
	DROP TABLE DEAN
END
GO 
CREATE TABLE NHANVIEN
(
	HONV NVARCHAR(10),
	TENLOT NVARCHAR(20),
	TENNV NVARCHAR(10),
	MANV VARCHAR(10),
	NGSINH SMALLDATETIME,
	DCHI NVARCHAR(100),
	PHAI NVARCHAR(3),
	LUONG NUMERIC,
	MA_NQL VARCHAR(10),
	PHG INT
	PRIMARY KEY(MANV)
)
DROP TABLE NHANVIEN
--THEM DU LIEU CHO TABLE NHANVIEN

INSERT INTO NHANVIEN VALUES('CAO','SI','KHI','123123456','08/09/1986','123 Le Loi Q1 TpHCM','NAM',15000,'123456789',5)
INSERT INTO NHANVIEN VALUES('DINH','BA','TIEN','123456789','09/01/1955','731 Tran Hung Dao Q1 TpHCM','NAM',30000,'123456789',5)
INSERT INTO NHANVIEN VALUES('TRUONG','LE','DOAN','147852369','05/15/1986','22/41/2 Le Van Tho F11 Go Vap','NU',16500,'147852369',1)
INSERT INTO NHANVIEN VALUES('NGUYEN','THANH','TUNG','333445555','08/12/1945','638 Nguyen Van Cu Q5 TpHCM','NAM',40000,'123456789',5)
INSERT INTO NHANVIEN VALUES('LE','HUYNH','DUC','666884444','01/06/1986','126 Nguyen Van Cu Q5 Tp Hcm','NAM',27000,'666884444',1)
INSERT INTO NHANVIEN VALUES('VUONG','NGOC','QUYEN','888665555','09/15/1965','414 NO TRANG LONG Q BINH THANH TPHCM','NAM',29400,'999887777',4)
INSERT INTO NHANVIEN VALUES('LE','THI','NHAN','987654321','06/20/1931','291 Ho Van Hue Qphu Nhuan TpHCM','NU',43000,'123456789',5)
INSERT INTO NHANVIEN VALUES('VO','DUC','MINH','987987987','09/16/1985','414 NO TRANG LONG Q BINH THANH TPHCM','NAM',30000,'123456789',5)
INSERT INTO NHANVIEN VALUES('BUI','THUY','VU','999887777','07/19/1958','332 Nguyen Thai Hoc Q1 TpHCM','NAM',25000,'123456789',4)

SELECT * FROM NHANVIEN

--TAO TABLE PHONGBAN

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='PHONGBAN')
BEGIN 
	DROP TABLE DEAN
END
GO 
CREATE TABLE PHONGBAN
(
	TENPHG NVARCHAR(10),
	MAPHG INT,
	TRPHG VARCHAR(10),
	NG_NHANCHUC SMALLDATETIME
	PRIMARY KEY(MAPHG)
)
DROP TABLE PHONGBAN
--THEM DU LIEU CHO TABLE PHONGBAN

INSERT INTO PHONGBAN VALUES('QUAN LI',1,'666884444','06/19/1971')
INSERT INTO PHONGBAN VALUES('TO CHUC',2,'987987987','01/15/2001')
INSERT INTO PHONGBAN VALUES('DIEU HANH',4,'123456789','01/01/1985')
INSERT INTO PHONGBAN VALUES('NGHIEN CUU',5,'333445555','05/22/1978')

SELECT * FROM PHONGBAN

--TAO TABLE PHANCONG

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='PHANCONG')
BEGIN 
	DROP TABLE DEAN
END
GO 
CREATE TABLE PHANCONG
(
	MA_NVIEN VARCHAR(10),
	SODA INT,
	THOIGIAN FLOAT
		PRIMARY KEY (MA_NVIEN,SODA)
)


--THEM DU LIEU CHO TABLE PHANCONG

INSERT INTO PHANCONG(MA_NVIEN,SODA) VALUES('123123456',2)
INSERT INTO PHANCONG(MA_NVIEN,SODA) VALUES('123123456',40)
INSERT INTO PHANCONG(MA_NVIEN,SODA,THOIGIAN) VALUES('123456789',40,10.00)
INSERT INTO PHANCONG(MA_NVIEN,SODA) VALUES('147852369',40)
INSERT INTO PHANCONG(MA_NVIEN,SODA) VALUES('333445555',2)
INSERT INTO PHANCONG(MA_NVIEN,SODA) VALUES('333445555',40)
INSERT INTO PHANCONG(MA_NVIEN,SODA) VALUES('666884444',40)
INSERT INTO PHANCONG(MA_NVIEN,SODA) VALUES('888665555',2)
INSERT INTO PHANCONG(MA_NVIEN,SODA) VALUES('987654321',40)
INSERT INTO PHANCONG(MA_NVIEN,SODA) VALUES('987987987',40)
INSERT INTO PHANCONG(MA_NVIEN,SODA) VALUES('999887777',40)

SELECT * FROM PHANCONG

--TAO TABLE THANNHAN 

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='THANNHAN')
BEGIN 
	DROP TABLE DEAN
END
GO 
CREATE TABLE THANNHAN
(
	MA_NVIEN VARCHAR(10),
	TENTN NVARCHAR(30),
	PHAI VARCHAR(10),
	NGSINH DATETIME,
	QUANHE VARCHAR(30)
	PRIMARY KEY(MA_NVIEN,TENTN)
)

--THEM DU LIEU CHO TABLE THANNHAN

INSERT INTO THANNHAN VALUES('333445555','Chau Long','NAM','01/02/2005','CHONG')
INSERT INTO THANNHAN VALUES('333445555','KHANG','NAM','10/25/1973','CON TRAI')
INSERT INTO THANNHAN VALUES('333445555','NGOC PHI','NU','04/05/1976','CON GAI')
INSERT INTO THANNHAN VALUES('987654321','DANG','NAM','02/29/1932','VO CHONG')
INSERT INTO THANNHAN VALUES('999887777','Toan','NAM','01/02/1986','CHONG')
SELECT *FROM THANNHAN
